# -*- coding: UTF-8 -*-
"""
@auth:buxiangjie
@date:2020-10-11 09:26:00
@describe: 数据库连接配置
"""
import pymysql
import json
import traceback
import sys
import os
import allure

from typing import Optional
from log.ulog import logger_
from common.common_func import Common
from common.common_func import Encoder


class ConnMysql:
	"""
	连接mysql
	"""

	@staticmethod
	def conn_database(environment: str, source: Optional[str] = "saas") -> pymysql.Connect:
		"""
		数据库连接
		"""
		yaml_data = Common.get_yaml_data("config", "database.yaml")
		config = yaml_data[environment][source]
		config["cursorclass"] = pymysql.cursors.DictCursor
		if not config.get("password", None):
			config["password"] = None
		else:
			config["password"] = str(config["password"])
		connect = pymysql.connect(**config)
		return connect

	@staticmethod
	def exec_update(
			sql: str,
			environment: str,
			source: Optional[str] = "saas"
	):
		"""执行更新语句"""
		conn = None
		cur = None
		try:
			conn = ConnMysql.conn_database(environment, source)
			cur = conn.cursor()
			logger_.info(f"执行更新语句:{sql}")
			cur.execute(sql)
			conn.commit()
		except Exception:
			conn.rollback()
			logger_.error(traceback.format_exc())
		finally:
			cur.close()
			conn.close()

	@staticmethod
	def exec_insert(
			sql: str,
			environment: str,
			source: Optional[str] = "saas"
	):
		"""执行插入语句"""
		conn = None
		cur = None
		try:
			conn = ConnMysql.conn_database(environment, source)
			cur = conn.cursor()
			logger_.info(f"执行插入语句:{sql}")
			cur.execute(sql)
			conn.commit()
		except Exception:
			conn.rollback()
			logger_.error(traceback.format_exc())
		finally:
			cur.close()
			conn.close()

	@staticmethod
	def exec_delete(
			sql: str,
			environment: str,
			source: Optional[str] = "saas"
	):
		"""执行删除语句"""
		conn = None
		cur = None
		try:
			conn = ConnMysql.conn_database(environment, source)
			cur = conn.cursor()
			logger_.info(f"执行删除语句:{sql}")
			cur.execute(sql)
			conn.commit()
		except Exception:
			conn.rollback()
			logger_.error(traceback.format_exc())
		finally:
			cur.close()
			conn.close()

	@staticmethod
	@allure.step("执行查询sql")
	def exec_select(
			sql: str,
			environment: str,
			source: Optional[str] = "saas"
	) -> list:
		"""执行查询语句"""
		conn = None
		cur = None
		try:
			conn = ConnMysql.conn_database(environment, source)
			cur = conn.cursor()
			logger_.info(f"""执行查询语句:{sql}""")
			cur.execute(sql)
			fetch = cur.fetchall()
			result = None
			if len(fetch) > 0:
				result = json.loads(json.dumps(fetch, cls=Encoder))
			logger_.info(f"查询结果为:{result}")
			Common.get_rep_text(result)
			return result
		except Exception:
			logger_.error(traceback.format_exc())
		finally:
			cur.close()
			conn.close()

	@staticmethod
	def get_sub_table(environment: str, asset_id: str) -> str:
		"""分表计算"""
		table = None
		if environment == "test":
			table = str((int(asset_id) % 8 + 1))
		elif environment in ("qa", "prod"):
			table = str((int(asset_id) >> 22) % 8 + 1)
		return table

	@staticmethod
	def get_column(environment: str, source: str, table_name: str, col: list):
		"""获取剔除后的字段"""
		if len(col) == 1:
			col_str = f"column_name <> '{col[0]}'"
		elif len(col) > 1:
			new_list = []
			for i in col:
				new_list.append(f"column_name <> '{i}' ")
			col_str = "and ".join(new_list)
		col_sql = f"""
					select GROUP_CONCAT(column_name , ',') as col from information_schema.`COLUMNS` 
					where {col_str}
					and table_name='{table_name}'
					group by table_name;
				"""
		res = str(ConnMysql.exec_select(environment, col_sql, source)[0]["col"]).replace(",,", ",")
		logger_.info(f"获取要查询的字段")
		return res
